vim /root/dws_inc_aml_customer_risk_profile.sql

-- 客户风险画像宽表
INSERT INTO TABLE dws.dws_aml_customer_risk_profile PARTITION (etl_date = '${hivevar:etl_dt}')
SELECT
    DAC.customer_id AS customer_sk
    ,DAC.risk_level
    ,COALESCE(SUM(DAA.current_balance),0) AS total_balance
    ,MAX(AAM.last_txn_date) AS last_txn_date
    ,COALESCE(SUM(trigger_txn_count),0) AS alert_count
    ,COUNT(DISTINCT ASR.matched_list_id) AS screening_hit_count
    ,CASE WHEN FAS.report_status = '已提交' THEN TRUE ELSE FALSE END AS str_submitted
    ,CASE
        WHEN DAC.INDUSTRY_TYPE IN ('金融业','批发和零售业','住宿和餐饮业','租赁和商务服务业','房地产业','文化体育娱乐业')
            THEN TRUE
        ELSE FALSE
    END AS industry_risk_flag
FROM DWD.dim_aml_customer DAC
LEFT JOIN DWD.dim_aml_account DAA
ON DAC.customer_id = DAA.customer_id
LEFT JOIN ODS.ods_aml_account_master AAM
ON AAM.customer_id = DAC.customer_id
LEFT JOIN DWD.fact_aml_alert FAA
ON FAA.customer_sk = DAC.customer_id
LEFT JOIN ODS.ods_aml_screening_result ASR
ON ASR.target_id = DAC.customer_id
LEFT JOIN DWD.fact_aml_str_report FAS
ON FAS.customer_sk = DAC.customer_id
where etl_date = '${hivevar:etl_dt}'
GROUP BY DAC.customer_id, DAC.risk_level,FAS.report_status,DAC.INDUSTRY_TYPE ;

-- 交易行为分析
vim /root/dws_inc_aml_transaction_behavior.sql

INSERT INTO TABLE DWS.DWS_AML_TRANSACTION_BEHAVIOR PARTITION (etl_date = '${hivevar:etl_dt}')
SELECT
    AT.ETL_DATE AS DATE_SK
    ,AT.TXN_TYPE AS TXN_TYPE
    ,AT.TXN_CHANNEL AS CHANNEL_TYPE
    ,AT.IS_HIGH_RISK AS IS_HIGH_RISK
    ,COUNT(*) AS TRANSACTION_COUNT
    ,SUM(AT.AMOUNT) AS TOTAL_AMOUNT
    ,AVG(AT.AMOUNT) AS AVERAGE_AMOUNT
    ,(SUM(CASE WHEN AT.IS_CROSS_BORDER = 1 THEN 1 ELSE 0 END)/COUNT(*))*100 AS CROSS_BORDER_RATIO
FROM DWD.FACT_AML_TRANSACTION AT
where etl_date = '${hivevar:etl_dt}'
GROUP BY AT.ETL_DATE, AT.TXN_TYPE, AT.TXN_CHANNEL, AT.IS_HIGH_RISK;

-- 风险监控
vim /root/dws_inc_aml_risk_dashboard.sql

insert into dws.dws_aml_transaction_behavior partition (etl_date = '${hivevar:etl_dt}')
SELECT
    C.start_date AS date_sk
    ,count(distinct c.cust_no) as new_customer_count
    ,count(case when c.risk_level in ('高','极高') then 1 else null end) as high_risk_customer_count
    ,count(a.trigger_txn_count) as alert_count
    ,count(case when a.alert_status = '确认' then 1 else null end) as confirmed_alert_count
    ,count(case when r.report_status = '已提交' then 1 else null end) as str_submitted_count
    ,SUM(CASE WHEN T.is_cross_border THEN 1 ELSE 0 END)  as cross_border_txn_count
    ,count(T.is_cross_border) as total_txn_count
FROM DWD.dim_aml_customer C
LEFT JOIN DWD.fact_aml_alert A
ON C.cust_no = A.customer_sk
LEFT JOIN DWD.fact_aml_str_report R
ON R.customer_sk = A.customer_sk
LEFT JOIN DWD.fact_aml_transaction T
ON T.customer_sk = R.customer_sk
where etl_date = '${hivevar:etl_dt}'
GROUP BY C.start_date;




#!/bin/bash
etl_dt=`date -d '-1 days' +"%Y-%m-%d"`
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_customer.sql        >>/aml/dwd/dim_aml_customer_$etl_date.log       &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_account.sql         >>/aml/dwd/dim_aml_account.sql_$etl_date.log    &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_watchlist.sql       >>/aml/dwd/dim_aml_watchlist_$etl_date.log      &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_rule.sql            >>/aml/dwd/dim_aml_rule_$etl_date.log           &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_transaction.sql    >>/aml/dwd/fact_aml_transaction_$etl_date.log   &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_screening.sql      >>/aml/dwd/fact_aml_screening_$etl_date.log     &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_alert.sql          >>/aml/dwd/fact_aml_alert_$etl_date.log         &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_str_report.sql     >>/aml/dwd/fact_aml_str_report_$etl_date.log    &

nohup hive -hivevar etl_dt=$etl_dt -f /root/dws.dws_aml_customer_risk_profile.sql      >>/aml/dwd/dws.dws_aml_customer_risk_profile_$etl_date.log     &
nohup hive -hivevar etl_dt=$etl_dt -f /dws_inc_aml_transaction_behavior.sql          >>/aml/dws_inc_aml_transaction_behavior_$etl_date.log         &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dws_inc_aml_risk_dashboard.sql     >>/aml/dwd/dws_inc_aml_risk_dashboard_$etl_date.log    &

 #定时抽
 crontab -e
30 1 * * *  bash /root/dsc.sh

